package com.hamilton.erik.pt.core.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.hamilton.erik.pt.core.dao.EnclosureDAO;
import com.hamilton.erik.pt.core.db.H2JdbcAccess;
import com.hamilton.erik.pt.core.model.Enclosure;
import com.hamilton.erik.pt.core.util.SqlUtil;

public class H2EnclosureDAO implements EnclosureDAO {

	private final String ENCLOSURE_DB_TABLE = "APPLICATION.ENCLOSURE";
	
	private final String INSERT_ENCLOSURE = 
			String.format(
				"INSERT INTO %s (ANIMAL_ID, ENCLOSURE_TYPE, SHAPE, HEIGHT, " +
				"LENGTH, WIDTH, VOLUME, VOLUME_UNIT) " +
				"VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
				ENCLOSURE_DB_TABLE);
	
	private final String FIND_ENCLOSURE =
			String.format(
				"SELECT * FROM %s WHERE ANIMAL_ID = ?", ENCLOSURE_DB_TABLE);
	
	private final String UPDATE_ENCLOSURE =
			String.format(
				"UPDATE %s SET ENCLOSURE_TYPE=?, SHAPE=?, HEIGHT=?, LENGTH=?, " +
				"WIDTH=?, VOLUME=?, VOLUME_UNIT=? WHERE ID=?",
				ENCLOSURE_DB_TABLE);
	
	private final String DELETE_ENCLOSURE =
			String.format("DELETE FROM %s WHERE ID=?", ENCLOSURE_DB_TABLE);
	
	private final String DELETE_ALL_ENCLOSURES =
			String.format("DELETE FROM %s WHERE ANIMAL_ID=?", ENCLOSURE_DB_TABLE);
	
	@Override
	public int insertEnclosure(Enclosure enclosure) throws SQLException {

		int insertedRow = -1;
		Connection conn = null;
		
		try {
			conn = H2JdbcAccess.getInstance().getConnection();
			conn.setAutoCommit(false);
						
			PreparedStatement stmt = conn.prepareStatement(INSERT_ENCLOSURE);
		
			stmt.setLong(1, enclosure.getAnimalId());
			stmt.setString(2, enclosure.getEnclosureType());
			SqlUtil.setStringOrNull(3, stmt, enclosure.getShape().toString());
			SqlUtil.setDoubleOrNull(4, stmt, enclosure.getHeight());
			SqlUtil.setDoubleOrNull(5, stmt, enclosure.getLength());
			SqlUtil.setDoubleOrNull(6, stmt, enclosure.getWidth());
			SqlUtil.setDoubleOrNull(7, stmt, enclosure.getVolume());
			SqlUtil.setStringOrNull(8, stmt, enclosure.getVolumeUnit());
			
			insertedRow = stmt.executeUpdate();
			
			conn.commit();

			return insertedRow;
		} catch (SQLException e) {
			throw e;
		} finally {
			if(conn != null) try { conn.close(); } catch (Exception ignore) {}
		}
	}

	@Override
	public boolean deleteEnclosure(Long enclosureId) throws SQLException {

		int returnValue = -1;
		Connection conn = null;
		
		try {
			conn = H2JdbcAccess.getInstance().getConnection();
			conn.setAutoCommit(false);
						
			PreparedStatement stmt = conn.prepareStatement(DELETE_ENCLOSURE);

			stmt.setLong(1, enclosureId);
			
			returnValue = stmt.executeUpdate();
			
			conn.commit();

			if(returnValue > 0) {
				return true;
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			if(conn != null) try { conn.close(); } catch (Exception ignore) {}
		}
		
		return false;
	}

	@Override
	public boolean deleteAllEnclosures(Long animalId) throws SQLException {

		int returnValue = -1;
		Connection conn = null;
		
		try {
			conn = H2JdbcAccess.getInstance().getConnection();
			conn.setAutoCommit(false);
						
			PreparedStatement stmt = conn.prepareStatement(DELETE_ALL_ENCLOSURES);

			stmt.setLong(1, animalId);
			
			returnValue = stmt.executeUpdate();
			
			conn.commit();

			if(returnValue > 0) {
				return true;
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			if(conn != null) try { conn.close(); } catch (Exception ignore) {}
		}
		
		return false;
	}

	@Override
	public Enclosure findEnclosure(Long animalId) throws SQLException {

		Connection conn = null;
		
		try {
			
			Enclosure newEnclosure = new Enclosure();
			newEnclosure.setAnimalId(animalId);
			
			conn = H2JdbcAccess.getInstance().getConnection();
						
			PreparedStatement stmt = conn.prepareStatement(FIND_ENCLOSURE);
		
			stmt.setLong(1, animalId);
			
			ResultSet rs = stmt.executeQuery();
			
			if(rs.next()) {	
				newEnclosure.setId(rs.getLong(1));
				newEnclosure.setAnimalId(rs.getLong(2));
				newEnclosure.setEnclosureType(rs.getString(3));
				newEnclosure.setShape(rs.getString(4));
				newEnclosure.setHeight(rs.getDouble(5));
				newEnclosure.setLength(rs.getDouble(6));
				newEnclosure.setWidth(rs.getDouble(7));
				newEnclosure.setVolume(rs.getDouble(8));
				newEnclosure.setVolumeUnit(rs.getString(9));
			}
			
			return newEnclosure;
			
		} catch (SQLException e) {
			throw e;
		} finally {
			if(conn != null) try { conn.close(); } catch (Exception ignore) {}
		}
	}

	@Override
	public int updateEnclosure(Enclosure enclosure) throws SQLException {

		int updatedRow = -1;
		Connection conn = null;
		
		try {
			conn = H2JdbcAccess.getInstance().getConnection();
			conn.setAutoCommit(false);
						
			PreparedStatement stmt = conn.prepareStatement(UPDATE_ENCLOSURE);
		
			stmt.setString(1, enclosure.getEnclosureType());
			SqlUtil.setStringOrNull(2, stmt, enclosure.getShape().toString());
			SqlUtil.setDoubleOrNull(3, stmt, enclosure.getHeight());
			SqlUtil.setDoubleOrNull(4, stmt, enclosure.getLength());
			SqlUtil.setDoubleOrNull(5, stmt, enclosure.getWidth());
			SqlUtil.setDoubleOrNull(6, stmt, enclosure.getVolume());
			SqlUtil.setStringOrNull(7, stmt, enclosure.getVolumeUnit());
			stmt.setLong(8, enclosure.getId());
			
			updatedRow = stmt.executeUpdate();
			
			conn.commit();

			return updatedRow;
		} catch (SQLException e) {
			throw e;
		} finally {
			if(conn != null) try { conn.close(); } catch (Exception ignore) {}
		}
	}

}
